package com.xxx.text;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class AddCourse {

    public static void main(String[] args) {
//        int a=Add();
//        if (a >0) {
//            System.out.println("添加成功");
//        }else {
//            System.out.println("添加失败");
//        }
//        int b=Update();
//        if (b >0) {
//            System.out.println("修改成功");
//        }else {
//            System.out.println("修改失败");
//        }
//        int c=Delete();
//        if (c >0) {
//            System.out.println("删除成功");
//        }else {
//            System.out.println("删除失败");
//        }
        List list;

        try {
            list=Select();
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }


    }
    public static int Add() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        int count=0;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接对象
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/studentgrade","root","123" );
            String sql="insert into course values (null,?,?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,"CSS");
            pstmt.setInt(2,24);
            count = pstmt.executeUpdate();
        } catch (ClassNotFoundException|SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }
    public static int Delete(){
        Connection connetion = null;
        PreparedStatement preparedStatement=null;
        int i=0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connetion = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentgrade","root","123");
            String sql="DELETE from course where cname=?";
            preparedStatement = connetion.prepareStatement(sql);
            preparedStatement.setString(1,"CSS");
            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                if(preparedStatement!=null)
                    preparedStatement.close();
                if(connetion!=null)
                    connetion.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return i;
        }

    }
    public static int Update(){
        Connection connetion=null;
        PreparedStatement preparedStatement=null;
        int i=0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connetion = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentgrade","root","123");
            String sql="update course set classHour=? where cname=?";
            preparedStatement = connetion.prepareStatement(sql);
            preparedStatement.setInt(1,25);
            preparedStatement.setString(2,"ajax");
            i = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (preparedStatement!=null)
                    preparedStatement.close();
                if(connetion!=null)
                    connetion.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return i;
        }

    }
    public static List<Course> Select() throws SQLException {
        Connection conn=null;
        PreparedStatement pstmt=null;
        List<Course> list=new ArrayList<>();
        ResultSet resultSet=null;
        try {
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/studentgrade","root","123");
            String sql="select * from course";
            pstmt=conn.prepareStatement(sql);
             resultSet = pstmt.executeQuery();
            while (resultSet.next())
            {
                Course course = new Course();
                course.setCno(resultSet.getInt(1));
                course.setCname(resultSet.getString(2));
                course.setCno(resultSet.getInt(3));
                list.add(course);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

                resultSet.close();
                pstmt.close();
                conn.close();
                return list;
        }
    }
}
